﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DiscoveryLogic.Analytics.DataObjects;
using DiscoveryLogic.Common.Utilities;

namespace DiscoveryLogic.Analytics.BusinessLogic.Searches
{
    public class EntitySearchUtil
    {
        public static List<EntityBase> Search(EntityType entityType, string fieldName, string searchText)
        {
            switch(entityType)
            {
                case EntityType.Grants:
                    return SearchGrants(fieldName, searchText);
                case EntityType.Organizations:
                    return SearchOrgs(fieldName, searchText);
                case EntityType.Persons:
                    return SearchPersons(fieldName, searchText);
                case EntityType.Publications:
                    return SearchPubs(fieldName, searchText);
                default:
                    return null;
            }
        }

        public static List<EntityBase> SearchGrants(string fieldName, string searchText)
        {
            Dictionary<string,string> fieldMappings = EntitySearchUtil.GetGrantsFieldMappings();
            if (fieldMappings.ContainsKey(fieldName))
                fieldName = fieldMappings[fieldName];
            List<EntityBase> grants = new List<EntityBase>();
            string sql =
                @"select distinct top 500  
a.*,
b.ACTIVITY_CODE,
b.APPL_TYPE_CODE, 
b.FY,
b.SUPPORT_YEAR from 
dbo.Project a inner join dbo.Appl b
on a.projectid=b.project_id";
            string connStr = @"server=dls18sqldev1\dev1;database=nihproject;integrated security=sspi;";
            TableColumn col = DbUtil.GetTableColumn(DataProviderType.MSSQL, connStr, sql, fieldName);
            if(string.IsNullOrEmpty(col.ColumnName))
                throw new Exception("Unable to find table field");
            searchText = FieldDataType.ToMssqlDataValue(col.DbType, searchText);
            sql = string.Format(
                "{0} where {1}={2} order by b.FY desc",
                sql, fieldName, searchText);
            DataTable dt = DbUtil.GetTableDataBySQL(connStr, "Grants", sql);
            if(dt==null && dt.Rows.Count==0)
                throw new Exception("Unable to retrieve grants");
            List<string> pkAdded=new List<string>();
            foreach(DataRow dr in dt.Rows)
            {
                GrantDO grant=new GrantDO();
                grant.ProjectID = dr["ProjectID"] == DBNull.Value ? 0 : int.Parse(dr["ProjectID"].ToString());
                grant.Abstract = dr["Abstract"] == DBNull.Value ? string.Empty : dr["Abstract"].ToString();
                grant.AccessionNum = dr["AccessionNum"] == DBNull.Value ? 0 : int.Parse(dr["AccessionNum"].ToString());
                grant.ActivityCode = dr["ACTIVITY_CODE"].ToString();
                grant.ApplTypeCode =int.Parse(dr["APPL_TYPE_CODE"].ToString());
                grant.PhsAdminOrg = dr["AdminPHSOrgCode"].ToString();
                grant.SerialNum = int.Parse(dr["SerialNum"].ToString());
                grant.SupportYear = int.Parse(dr["SUPPORT_YEAR"].ToString());
                grant.GrantNumber = string.Format(
                    "{0} {1}{2} {3}-{4}",
                    grant.ApplTypeCode, grant.ActivityCode, grant.PhsAdminOrg,
                    Padding(grant.SerialNum, 6), Padding(grant.SupportYear, 2));
                grant.Title = dr["Title"] == DBNull.Value ? "" : dr["Title"].ToString();

                string pk = grant.PKValue();
                if (!string.IsNullOrEmpty(pk) && !pkAdded.Contains(pk))
                {
                    grants.Add(grant);
                    pkAdded.Add(pk);
                }
            }

            return grants;
        }

        public static List<EntityBase> SearchOrgs(string fieldName, string searchText)
        {
            Dictionary<string, string> fieldMappings = 
                EntitySearchUtil.GetOrgFieldMappings();
            if (fieldMappings.ContainsKey(fieldName))
                fieldName = fieldMappings[fieldName];
            List<EntityBase> orgs = new List<EntityBase>();
            string sql =
                @"select  top 500 
b.CITY_NAME,
c.COUNTRY_NAME,
d.DEPT_NAME,
a.DUNS_ID,
a.EIN,
a.ORG_NAME,
b.ZIP_CODE,
e.STATE_CODE
from 
IRDB.External_Orgs_MV a 
left join IRDB.External_Org_Addresses_T b on a.EXTERNAL_ORG_ID=b.EXTERNAL_ORG_ID 
left join IRDB.Countries_T c on b.COUNTY_CODE=c.NIH_STATE_COUNTRY_CODE
left join IRDB.Ext_Org_Depts_MV d on a.EXTERNAL_ORG_ID=d.EXTERNAL_ORG_ID
left join IRDB.States_T e on b.STATE_CODE=e.STATE_CODE";
            string connStr = @"server=dls18sqldev1\dev1;database=IMPACII_Mirror;integrated security=sspi;";
            TableColumn col = DbUtil.GetTableColumn(DataProviderType.MSSQL, connStr, sql, fieldName);
            if (string.IsNullOrEmpty(col.ColumnName))
                throw new Exception("Unable to find table field");
            searchText = FieldDataType.ToMssqlDataValue(col.DbType, searchText);
            sql = string.Format(
                "{0} where {1}={2}",
                sql, fieldName, searchText);
            DataTable dt = DbUtil.GetTableDataBySQL(connStr, "Grants", sql);
            if (dt == null && dt.Rows.Count == 0)
                throw new Exception("Unable to retrieve grants");
            foreach (DataRow dr in dt.Rows)
            {
                ExternalOrgDO org = new ExternalOrgDO();
                string colName = fieldMappings["City"];
                org.City = dr[colName] == DBNull.Value ? "" : dr[colName].ToString();
                colName = fieldMappings["Country"];
                org.Country = dr[colName] == DBNull.Value ? "" : dr[colName].ToString();
                colName = fieldMappings["Department"];
                org.Department = dr[colName] == DBNull.Value ? "" : dr[colName].ToString();
                colName = fieldMappings["DunsID"];
                org.DunsID = dr[colName] == DBNull.Value ? "" : dr[colName].ToString();
                colName = fieldMappings["EIN"];
                org.EIN = dr[colName] == DBNull.Value ? "" : dr[colName].ToString();
                colName = fieldMappings["ExternalOrgID"];
                org.ExternalOrgID = dr[colName] == DBNull.Value ? 0 : int.Parse(dr[colName].ToString());
                colName = fieldMappings["OrgName"];
                org.OrgName = dr[colName] == DBNull.Value ? "" : dr[colName].ToString();
                colName = fieldMappings["PostalCode"];
                org.PostalCode = dr[colName] == DBNull.Value ? "" : dr[colName].ToString();
                colName = fieldMappings["Region"];
                org.Region = dr[colName] == DBNull.Value ? "" : dr[colName].ToString();
                orgs.Add(org);
            }

            return orgs;
        }

        public static List<EntityBase> SearchPersons(string fieldName, string searchText)
        {
            Dictionary<string, string> fieldMappings =
                EntitySearchUtil.GetPersonFieldMappings();
            if (fieldMappings.ContainsKey(fieldName))
                fieldName = fieldMappings[fieldName];
            List<EntityBase> persons = new List<EntityBase>();
            string sql =
                @"select top 500 
a.SSN, 
a.BIRTH_DATE,
a.FIRST_NAME,
a.LAST_NAME,
a.MI_NAME,
a.NAME_PREFIX,
a.NAME_SUFFIX
from 
IRDB.Person_Unrestricted_VW a";
            string connStr = @"server=dls18sqldev1\dev1;database=IMPACII_Mirror;integrated security=sspi;";
            TableColumn col = DbUtil.GetTableColumn(DataProviderType.MSSQL, connStr, sql, fieldName);
            if (string.IsNullOrEmpty(col.ColumnName))
                throw new Exception("Unable to find table field");
            searchText = FieldDataType.ToMssqlDataValue(col.DbType, searchText);
            sql = string.Format(
                "{0} where {1}={2}",
                sql, fieldName, searchText);
            DataTable dt = DbUtil.GetTableDataBySQL(connStr, "Grants", sql);
            if (dt == null && dt.Rows.Count == 0)
                throw new Exception("Unable to retrieve grants");
            foreach (DataRow dr in dt.Rows)
            {
                PersonDO person=new PersonDO();
                person.SSN = dr["SSN"] == DBNull.Value ? "" : dr["SSN"].ToString();
                person.Degree = string.Empty;
                person.DOB = dr["BIRTH_DATE"]==DBNull.Value?DateTime.MinValue:DateTime.Parse(dr["BIRTH_DATE"].ToString());
                person.Email = "";
                person.FirstName = dr["FIRST_NAME"] == DBNull.Value ? "" : dr["FIRST_NAME"].ToString();
                person.LastName = dr["LAST_NAME"] == DBNull.Value ? "" : dr["LAST_NAME"].ToString();
                person.MidName = dr["MI_NAME"] == DBNull.Value ? "" : dr["MI_NAME"].ToString();
                person.NameSuffix = dr["NAME_SUFFIX"] == DBNull.Value ? "" : dr["NAME_SUFFIX"].ToString();
                person.Telephone = "";
                person.Title = "";
                persons.Add(person);
            }


            return persons;
        }

        public static List<EntityBase> SearchPubs(string fieldName, string searchText)
        {
            Dictionary<string, string> fieldMappings =
                EntitySearchUtil.GetPubFieldMappings();
            if (fieldMappings.ContainsKey(fieldName))
                fieldName = fieldMappings[fieldName];
            List<EntityBase> pubs = new List<EntityBase>();
            string sql =
                @"select top 500 
a.PMID,
a.Affiliation,
a.AuthorList,
c.Title as JournalName,
a.ArticleDate,
a.ArticleTitle
from dbo.Citation a 
left join dbo.JournalIssue b on a.JournalIssueID=b.JournalIssueID 
left join dbo.Journal c on c.JournalID=b.JournalID";
            string connStr = @"server=dls18sqldev1\dev1;database=medline;integrated security=sspi;";
            TableColumn col = DbUtil.GetTableColumn(DataProviderType.MSSQL, connStr, sql, fieldName);
            if (string.IsNullOrEmpty(col.ColumnName))
                throw new Exception("Unable to find table field");
            searchText = FieldDataType.ToMssqlDataValue(col.DbType, searchText);
            sql = string.Format(
                "{0} where {1}={2}",
                sql, fieldName, searchText);
            DataTable dt = DbUtil.GetTableDataBySQL(connStr, "Grants", sql);
            if (dt == null && dt.Rows.Count == 0)
                throw new Exception("Unable to retrieve grants");
            foreach (DataRow dr in dt.Rows)
            {
                PublicationDO pub = new PublicationDO();
                pub.PMID = int.Parse(dr["PMID"].ToString());
                pub.Affiliation = dr["Affiliation"] == DBNull.Value ? "" : dr["Affiliation"].ToString();
                pub.Authors = dr["AuthorList"] == DBNull.Value ? "" : dr["AuthorList"].ToString();
                pub.Title = dr["ArticleTitle"] == DBNull.Value ? "" : dr["ArticleTitle"].ToString();
                pub.PubDate = dr["ArticleDate"] == DBNull.Value
                                  ? DateTime.MinValue
                                  : DateTime.Parse(dr["ArticleDate"].ToString());
                pub.Journal = dr["JournalName"] == DBNull.Value ? "" : dr["JournalName"].ToString();
                pubs.Add(pub);
            }

            return pubs;
        }

        /// <summary>
        /// prop name -> db field 
        /// </summary>
        /// <returns></returns>
        public static Dictionary<string,string> GetGrantsFieldMappings()
        {
            Dictionary<string,string> mappings = new Dictionary<string, string>();
            mappings.Add("Abstract", "Abstract");
            mappings.Add("AccessionNum", "AccessionNum");
            mappings.Add("ActivityCode", "ACTIVITY_CODE");
            mappings.Add("ApplTypeCode", "APPL_TYPE_CODE");
            mappings.Add("PhsAdminOrg", "AdminPHSOrgCode");
            mappings.Add("SerialNum", "SerialNum");
            mappings.Add("Title", "Title");
            mappings.Add("SupportYear", "SUPPORT_YEAR");
            return mappings;
        }

        public static Dictionary<string,string> GetOrgFieldMappings()
        {
            Dictionary<string,string> mappings=new Dictionary<string, string>();
            mappings.Add("City","CITY_NAME");
            mappings.Add("Country","COUNTRY_NAME");
            mappings.Add("Department","DEPT_NAME");
            mappings.Add("DunsID","DUNS_ID");
            mappings.Add("EIN","EIN");
            mappings.Add("ExternalOrgID","EXTERNAL_ORG_ID");
            mappings.Add("OrgName","ORG_NAME");
            mappings.Add("PostalCode","ZIP_CODE");
            mappings.Add("Region", "STATE_CODE");
            return mappings;
        }

        public static Dictionary<string,string> GetPersonFieldMappings()
        {
            Dictionary<string,string> fieldMappings=new Dictionary<string, string>();
            fieldMappings.Add("DOB", "BIRTH_DATE");
            fieldMappings.Add("FirstName", "FIRST_NAME");
            fieldMappings.Add("LastName","LAST_NAME");
            fieldMappings.Add("MidName","MI_NAME");
            fieldMappings.Add("NameSuffix","NAME_SUFFIX");
            fieldMappings.Add("SSN","SSN");
            return fieldMappings;
        }

        public static Dictionary<string,string> GetPubFieldMappings()
        {
            Dictionary<string,string> fieldMappings=new Dictionary<string, string>();
            fieldMappings.Add("Affiliation", "Affiliation");
            fieldMappings.Add("Authors", "AuthorList");
            fieldMappings.Add("Journal", "JournalName");
            fieldMappings.Add("PMID", "PMID");
            fieldMappings.Add("PubDate", "ArticleDate");
            fieldMappings.Add("Title", "ArticleTitle");
            return fieldMappings;
        }

        private static string Padding(int num, int digits)
        {
            string numstr = num.ToString();
            while(numstr.Length < digits)
                numstr = "0" + numstr;
            return numstr;
        }
    }
}
